Introducing Couchbase Time Series

Couchbase is excited to announce the release of a new time-series feature as part of Couchbase 7.2. This feature is built on top of the robust Couchbase distributed database architecture, which is designed to scale horizontally as your data grows, all while providing built-in redundancy and  high availability. This means that as your business grows and your time series data needs increase, Couchbase can effortlessly expand to meet those needs, making it an ideal solution for businesses of all sizes.

This innovative new technique to manage time series data opens up a whole new world of possibilities for Couchbase users. With the ability to store and analyze vast amounts of time series data using Couchbase SQL++ and SDKs. This allows users to leverage their existing knowledge and infrastructure, making it easy to set up and unlock powerful insights to explore data trends with ease. 

Key benefits of time series 

Time series data are stored in JSON documents in the Couchbase Multi Model database. It provides the same high performance, advanced caching for fast data retrieval and low latency. Couchbase Query SQL++ and Index service enhance the data retrieval capabilities to allow complex analytical query use cases.

Time Series data support in Couchbase provides these additional benefits:

1. 1. Efficient storage for large volume of time series data points
   2. Optimized data structure storage for time stamped data points
   3. New advanced time series query capabilities
   4. Low index storage requirement

Examples of time series use cases

Financial Trading – Financial trading relies on the analysis of large amounts of real-time data, including stock prices, currency rates, and commodity prices. Time-series data analysis can help traders identify trends and make informed decisions about buying and selling.

Internet of Things (IoT) Monitoring – IoT devices generate a large amount of time-series data, including temperature readings, energy consumption, and sensor data. This data can be analyzed in real-time to detect anomalies and predict equipment failures before they occur.

Predictive Maintenance – Many industries rely on expensive equipment and machinery, and downtime can be costly. By analyzing time-series data from sensors and other sources, organizations can predict when equipment is likely to fail, and proactively schedule maintenance to minimize downtime and maximize efficiency.

Key features of Couchbase Time Series

You can store time series data in Couchbase, using our SDK/SQL++ to load, and the advanced Analytical query capability with Global Secondary Index to query/analyze the data in the same way as with normal JSON documents.

Storage efficiency

Time series datasets are typically very large, with each data point consisting of attributes, such as timestamp, value(s), granularity and other related information. Efficient storage is critical as it can determine how fast the data can be queried for analysis. 

Couchbase time series uses two specifications to improve storage efficiency.

The use of arrays for data points – By its very nature, time series data are a series of data points. These data points share a common structure, e.g., time and values, or any other attributes that are associated with the time when the data point was collected.  

Using an array to store a set of the data points for a given range can greatly reduce the storage cost, compared to having to store each individual data point as a separate document in the database. 

The use of array position – Also note that data point array elements do not have an associated field name, but instead it relies on the position of the element in the array. In this example, the three elements in the array are: the observation date, the opening value, and the closing value of the stock.

Use EPOCH time – Epoch time is used instead of ISO date string in order to reduce the size of each data point, and improve processing time. 

Optimized query with a new _timeseries function

Couchbase Time Series feature includes a new _timeseries function. The function serves several purposes:

1. 1. It dynamically generates the time series objects from the ts_data array of arrays
   2. It efficiently streams the results when it is used with UNNEST, optimizes response time and memory usage
   3. It can automatically generate the timestamp for each data point when the ts_interval parameter is used
   4. It supports more advanced time series use cases for irregular time series data points interval, such as instead of a multiple sets of data points per timestamp

Please refer to the Couchbase time series documentation for more detail.

Optimized index storage

Due to the way Couchbase stores time series data, with each data point stored as an element in an array within a JSON document, we can optimize our database indexing strategy. Specifically, since each document contains both the start and end time of all the data points in the array, we can create a single index definition for each document. This means that even for large time series data sets with millions of data points, we can store the data in just a few documents rather than one document per data point.

For example, a time series data set with 1 million data points can be stored in just 1,000 documents, assuming that each ts_data array can store up to 1,000 data elements and the document size remains below the 20MB Couchbase JSON document limit. This not only reduces the number of documents needed to store the data, but it also leads to smaller index sizes, improving database performance and reducing disk space requirements.

In short, by taking advantage of Couchbase’s ability to store time series data as arrays within JSON documents, we can optimize our indexing strategy to significantly reduce the number of documents needed and reduce the index size, resulting in faster query performance and more efficient use of storage resources.

Data Retention 

Time Series documents are stored as standard JSON documents in Couchbase. So the same Time-To-Live (TTL) can be set during the data load process.

Example walkthrough

So let’s walk through the process of loading an actual stock price data set into Couchbase using the time series data model as defined by the feature.

As I have described above, the Couchbase time series feature requires the JSON document in this specific format:

Follow the steps below if you need to convert your own time series data into the above format.

The data set used here is for the XYZ Inc stock price for 2013-2015. 

XYZ_data.csv

Migrate Data into Couchbase time series data structure

1. Load the CSV file into a Couchbase collection:

The import will create a JSON document in collection c1 for each data point.

2. Use SQL++ to transform the collection c1 into the time series data structure, then insert into the collection c3:

The SQL++ performs an INSERT SELECT and creates a single document with the structure required for Couchbase time series processing. Note that the ts_data array contains the entire 2013 daily closing price data points. 

3. Repeat the INSERT/SELECT for 2014 and 2015.

Data Ingestion Strategies

There are several scenarios that you may have for incremental loading of your Time Series JSON documents.

1. 1. Adding a range of data points as a new JSON document – For this scenario you can use the above SQL++ INSERT. You just need to ensure that the data points ranges do not overlap existing documents.
   2. Adding a range of data points to an existing JSON document – Here you have two options:
      • Replace the entire document using UPSERT/SELECT as in the INSERT/SELECT
      • Use Couchbase SDK to append only the new item data points 

Query the time series data

Before querying the data, you should create an index. Although this is not absolutely needed as we only have a few documents, even though each document consists of the entire year of the daily stock price. 

Next we define the range of data that we want the query to run on. Here we define an array with two elements, the start and end epoch time for 2013-01-01 and 2015-12-31.

View the time series data points

Use the _timeseries function as described above:

Results:

View the time series data with SQL++ Window function

Now that we can access the entire data set, we can use Couchbase SQL++ windows function to run a few advanced aggregation functions. This query returns the daily average for the stock, as well as a 7 day moving average.

Results:

Use SQL++ charting on time series data

In this example, we use the Couchbase Time Series feature and its Charting capability to track a popular trading strategy by tracking fast (5 day) moving averages against slow (30 day) moving averages.

The idea behind this strategy is to identify when the short term trend (FMA) is crossing above or below the long term (SMA). When the FMA crossed the SMA, it is considered a buy signal, and conversely a sell signal when the FMA crossed below the SMA.

Use SQL++ Common Table Expression with time series data

This analysis calculates the Relative Strength Index, i.e., the speed and change of the stock price movements to identify when a stock is overbought or sold. An RSI value > 70 may indicate that the stock is overbought and due for correction. Conversely, when the RSI < 30, it may indicate that the stock is oversold and due for a rebound.

Results:

Key Takeaways

Data Storage – The overall data storage for the Couchbase time series will depend how many data points you choose to pack into the array. If analysis is by hour, day, month, then pack the data points according to the period. Note that the storage can also be further reduced if a regular time series is used where the time element in the time series can be derived, and thus does not require storing of the epoch time element.

Data Element and Data Retention with TTL – The maximum size of the JSON document in Couchbase is 20MB. While this could mean you can pack a large number of data points into the time series array, you should also be aware that the Time To Live setting is at the document level, and not the array element level. 

Data Ingestion – Application is responsible for the data ingestion strategy.  How to pack the time series data point into the arrays, and array size. Meaning it has to decide whether to append to an existing document, or start a new one.

To learn more, see the Couchbase time series documentation.